using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Management;
using System.Text;
using System.Net.Mail;
using System.IO;

public partial class InstanceDetail : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.btnDelete.Attributes.Add("OnCLick", "return confirm('Are you sure you want to delete this Instance and all associated databases from the repository?');");
            this.lnkUpdate.Attributes.Add("OnCLick", "return confirm('Are you sure you want to Update this Instance?');");
            this.lnkViewSystemProcesses.NavigateUrl=@"~/InstanceSysProcesses.aspx?InstanceName="+Request.QueryString["InstanceName"].ToString();
            this.lnkAddNote.NavigateUrl = "~/AddInstanceNote.aspx?InstanceName="+Request.QueryString["InstanceName"].ToString();
           
            if (Convert.ToBoolean(Session["sAdministrator"].ToString()) == false)
            {
                this.btnDelete.Visible = false;
                this.btnLoadRepository.Visible = false;                
                this.btnLoadRepository.Visible = false;
                this.lnkUpdate.Visible = false;
                this.lnkAddNote.Visible = false;
                this.lnkSendEMail.Visible = false;
                this.GridView1.Enabled = false;
                this.ddlBusinessCriticalLevel.Enabled = false;
                this.txtBusinessCriticalReason.Enabled = false;
                this.chkboxBusinessCritical.Enabled = false;
                this.chkboxDashBoardMonitored.Enabled = false;
                this.chkboxLoadIntoRepository.Enabled = false;
                this.txtPrimaryContactEMail.Enabled = false;
                this.txtBusinessCriticalReason.Enabled = false;
                this.txtPrimaryContactEMail.Enabled = false;
                this.txtShortDescription.Enabled = false;
            }

            DbCommand dbCommand5 = db.GetSqlStringCommand("SELECT BusinessCriticalLevel, BusinessCriticalResponse FROM InstanceBusinessCriticalLevel");
            DataSet ds5 = db.ExecuteDataSet(dbCommand5);                                     

            this.ddlBusinessCriticalLevel.DataSource = ds5;
            this.ddlBusinessCriticalLevel.DataTextField = "BusinessCriticalResponse";
            this.ddlBusinessCriticalLevel.DataValueField = "BusinessCriticalLevel";
            this.ddlBusinessCriticalLevel.DataBind();            
                                  
            BindData();

            this.lnkSendEMail.NavigateUrl = @"~/SendEMail.aspx?TO="+this.txtPrimaryContactEMail.Text.ToString()+"&TYPE=INSTANCE&Instance="+this.txtInstanceName.Text.ToString()+"&Database=NULL";
            
            HideAllDiv();
        }
    }
    protected void BindData()
    {
        string sqlQuery = "SELECT * FROM Instance WHERE InstanceName = '" + Request.QueryString["InstanceName"].ToString() + "'";
        DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlQuery);
        DataRow dr = ds.Tables[0].Rows[0];

        this.txtInstanceName.Text = dr["InstanceName"].ToString();
        this.txtShortDescription.Text = dr["InstanceShortDescription"].ToString();
        this.txtPrimaryContactEMail.Text = dr["PrimaryContactEMail"].ToString();
        this.chkboxDashBoardMonitored.Checked = Convert.ToBoolean(dr["DashboardActive"].ToString());
        this.chkboxLoadIntoRepository.Checked = Convert.ToBoolean(dr["LoadRepository"].ToString());
        this.chkboxBusinessCritical.Checked = Convert.ToBoolean(dr["BusinessCritical"].ToString());
        this.txtBusinessCriticalReason.Text = dr["BusinessCriticalReason"].ToString();

        if (chkboxBusinessCritical.Checked == true)
        {
            this.txtBusinessCriticalReason.Visible = true;
            this.lblBusinessCriticalReason.Visible = true;
            this.lblBusinsessCriticalLevel.Visible = true;
            this.ddlBusinessCriticalLevel.Visible = true;
        }
        else
        {
            this.txtBusinessCriticalReason.Visible = false;
            this.lblBusinessCriticalReason.Visible = false;
            this.lblBusinsessCriticalLevel.Visible = false;
            this.ddlBusinessCriticalLevel.Visible = false;
        }

        if (this.chkboxBusinessCritical.Checked == true)
        {
            this.ddlBusinessCriticalLevel.Items.FindByValue(dr["BusinessCriticalLevel"].ToString()).Selected = true;
        }        
           
        //Instance
        DbCommand dbCommand3 = db.GetSqlStringCommand("SELECT * FROM Instance with (nolock)  WHERE InstanceName ='" + Request.QueryString["InstanceName"].ToString() + "'");
        DataSet ds3 = db.ExecuteDataSet(dbCommand3);
        DataRow dr3 = ds3.Tables[0].Rows[0];

        this.lblInstanceDomainName.Text = dr3["InstanceDomainName"].ToString();        
        this.lblPortNumber.Text = dr3["InstancePortNumber"].ToString();
        lblEdition.Text = dr3["InstanceEdition"].ToString();
        lblVersion.Text = dr3["InstanceVersion"].ToString();
        lblProductLevel.Text = dr3["InstanceServicePack"].ToString();
        lblCollation.Text = dr3["InstanceCollation"].ToString();
        lblSQLLoginMode.Text = dr3["InstanceLoginMode"].ToString();
        lblOSVersion.Text = dr3["InstanceOSVersion"].ToString();
        lblArchitecture.Text = dr3["InstanceProcessorArchitecture"].ToString();
        lblMemory.Text = dr3["InstanceMemory"].ToString();
        lblMinMemory.Text = dr3["InstanceMinMemory"].ToString();
        lblMaxMemory.Text = dr3["InstanceMaxMemory"].ToString();        
        this.lblTraceFlags.Text = dr3["TraceFlags"].ToString();
        this.lblSQLServerServiceAccount.Text = dr3["SQLServerServiceAccount"].ToString();
        this.lblSQLAgentServiceAccount.Text = dr3["SQLAgentServiceAccount"].ToString();

        if (dr3["InstanceIsClustered"].ToString() == "True")
        {
            this.lblIsClustered.Text = "True, This instance is clustered";
        }
        else if (dr3["InstanceIsClustered"].ToString() == "False")
        {
            this.lblIsClustered.Text = "False, This instance is not clustered";
        }
        else
        {
            this.lblIsClustered.Text = "Unknown";
        }


        if (dr3["isVirtual"].ToString() == "1")
        {
            this.lblIsVirtual.Text = "True, This instance is virtualized";
        }
        else if (dr3["isVirtual"].ToString() == "0")
        {
            this.lblIsVirtual.Text = "False, This instance is physical";
        }
        else
        {
            this.lblIsVirtual.Text = "Unknown";
        }

        //Disk Usage
        string sqlQueryDriveSpaceUsage = "SELECT DriveName,DriveSizeGB,DriveFreeSpaceGB,DriveFreeSpacePct FROM DriveSpaceUsage Where InstanceName ='" + Request.QueryString["InstanceName"].ToString() + "'";
        DataSet dsDriveSpaceUsage = db.ExecuteDataSet(CommandType.Text, sqlQueryDriveSpaceUsage);
        this.gvDriveSpaceUsage.DataSource = dsDriveSpaceUsage;
        this.gvDriveSpaceUsage.DataBind();

        //Databases
        DbCommand dbCommand4 = db.GetSqlStringCommand("SELECT InstanceName,DatabaseName,TotalSizeMB,DataSizeMB,LogSizeMB,DataSpaceUsedMB,DataSpaceAvailableMB,DataSpaceAvailablePct,LastBackupFULL,LastBackupDIFF,LastBackupLOG,DatabaseStatus,CreateDate FROM [Database] Where InstanceName ='" + Request.QueryString["InstanceName"].ToString() + "'");
        DataSet ds4 = db.ExecuteDataSet(dbCommand4);
        gv.DataSource = ds4;
        gv.DataBind();

        //Scheduled Jobs
        string MachineName = String.Empty;
        string strInstanceName = String.Empty;
        strInstanceName = this.txtInstanceName.Text.ToString();

        if (strInstanceName.Contains(@"\"))
        {
            Int32 intLocation = strInstanceName.IndexOf(@"\");
            MachineName = strInstanceName.ToUpper().Substring(0, intLocation);
        }
        else
        {
            MachineName = strInstanceName.ToString().ToUpper();
        }

        try
        {
            //Connect up to SQL Server and collect Scheduled Job data
            SqlConnection cn = new SqlConnection(@"server=" + this.txtInstanceName.Text.ToString() + ";database=msdb;Integrated Security=SSPI;Pooling=False");
            cn.Open();
            SqlDataAdapter ad = new SqlDataAdapter(@"SELECT '" + this.txtInstanceName.Text.ToString() + "' as 'InstanceName', j.job_id,j.[name] as jobname, CASE [enabled] WHEN 1 THEN 'enabled' ELSE 'disabled' END AS 'enabled',SUBSTRING(CAST(last_run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(last_run_date AS CHAR(8)),2) + '/' + LEFT(CAST(last_run_date AS CHAR(8)),4)+' '+LEFT(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),2) AS 'lastrun',CASE last_run_outcome When 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' When 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In-Progress' WHEN 5 THEN 'Unknown' END AS 'status', LEFT(RIGHT('000000' + CAST(last_run_duration AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(last_run_duration AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(last_run_duration AS VARCHAR(10)),6),2) AS 'duration' FROM msdb..sysjobs j LEFT JOIN sysjobservers s on j.job_id = s.job_id ORDER BY j.[name]", cn);
            DataSet dsJobs = new DataSet();
            ad.Fill(dsJobs, "data");

            this.gvJobs.DataSource = dsJobs;
            this.gvJobs.DataBind();
            cn.Close();  
        }
        catch
        {
            this.divJobs.Visible = false;
            MessageBox.Show("Unable to connect to " + this.txtInstanceName.Text.ToString() +".  Verify that the instance is running.");
        }          
    }
    protected void gvJobs_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string status = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "status"));

            if (status == "Failed")
            {
                e.Row.BackColor = System.Drawing.Color.Red;
            }
        }
    }
    protected void lnkUpdate_Click(object sender, EventArgs e)
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

        string strBusinessCriticalLevel;
        string strBusinessCriticalReason;

        if (chkboxBusinessCritical.Checked == true)
        {
            strBusinessCriticalLevel = this.ddlBusinessCriticalLevel.SelectedItem.Value.ToString();
            strBusinessCriticalReason = this.txtBusinessCriticalReason.Text.Replace("'", "''").ToString();
        }
        else
        {
            strBusinessCriticalLevel = "";
            strBusinessCriticalReason = "";
        }   

        DbCommand dbCommand = db.GetSqlStringCommand("UPDATE Instance SET InstanceShortDescription = '" + this.txtShortDescription.Text + "'" +
                                           ",  LoadRepository = '" + Convert.ToBoolean(this.chkboxLoadIntoRepository.Checked) + "'" +
                                           ",  DashboardActive = '" + Convert.ToBoolean(this.chkboxDashBoardMonitored.Checked) + "'" +
                                           ",  BusinessCritical = '" + Convert.ToBoolean(this.chkboxBusinessCritical.Checked) + "'" +
                                           ",  BusinessCriticalReason = '" + strBusinessCriticalReason.ToString() + "'" +
                                           ",  BusinessCriticalLevel = '" + strBusinessCriticalLevel.ToString() + "'" +
                                           ",  PrimaryContactEMail = '" + this.txtPrimaryContactEMail.Text.ToString() + "'" +
                                           ",  LastUpdated = '" + System.DateTime.Now + "'" +
                                           " WHERE InstanceName = '" + Request.QueryString["InstanceName"].ToString() + "'");

        db.ExecuteNonQuery(dbCommand);
        
        BindData();
    }
    protected void btnLoadRepository_Click(object sender, EventArgs e)
    {
        Utility.LoadInstanceStatus(this.txtInstanceName.Text.ToString());
        Utility.LoadRepository(this.txtInstanceName.Text.ToString());
        BindData();
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        //Delete Databases
        DbCommand dbCommand1 = db.GetSqlStringCommand("DELETE FROM [Database] WHERE InstanceName = '" + this.txtInstanceName.Text.ToString()+"'");
        db.ExecuteNonQuery(dbCommand1);

        DbCommand dbCommand5 = db.GetSqlStringCommand("DELETE FROM [DatabaseFile] WHERE InstanceName = '" + this.txtInstanceName.Text.ToString() + "'");
        db.ExecuteNonQuery(dbCommand5);

        //DELETE Logins
        DbCommand dbCommand3 = db.GetSqlStringCommand("DELETE FROM [LOGIN_SQL] WHERE InstanceName = '" + this.txtInstanceName.Text.ToString() + "'");
        db.ExecuteNonQuery(dbCommand3);

        //Delete Instance
        DbCommand dbCommand2 = db.GetSqlStringCommand("DELETE FROM [Instance] WHERE InstanceName = '" + this.txtInstanceName.Text.ToString() + "'");
        db.ExecuteNonQuery(dbCommand2);
        
        //DELETE NOTES
        DbCommand dbCommand4 = db.GetSqlStringCommand("DELETE FROM [InstanceNote] WHERE InstanceName = '" + this.txtInstanceName.Text.ToString() + "'");
        db.ExecuteNonQuery(dbCommand4);

        //DELETE Alerts
        DbCommand dbCommand6 = db.GetSqlStringCommand("DELETE FROM [Alert] WHERE AlertInstanceName = '" + this.txtInstanceName.Text.ToString() + "'");
        db.ExecuteNonQuery(dbCommand6);        

        try
        {
            MailMessage msg = new MailMessage();

            msg.To.Add(Utility.DBANotificationEMailString());
            msg.Subject = "DBA Alert: " + this.txtInstanceName.Text.ToString() + " Instance Deleted";
            msg.Body = "-----------------------------------------------------" + " \r\n" +
                       "       Alert   : Instance Deleted" + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " SQL Instance  : " + this.txtInstanceName.Text.ToString() + " \r\n" +
                       " " + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       "END OF MESSAGE" + " \r\n";

            SmtpClient client = new SmtpClient();
            client.Send(msg);

        }
        catch
        {
            Response.Redirect("~/Dashboard.aspx");
        }

        Response.Redirect("~/Dashboard.aspx",true);

    }
    protected void HideAllDiv()
    {
        this.divJobs.Visible = false;
        this.DivDriveUsage.Visible = false;
        this.DivDatabases.Visible = false;
        this.DivConfiguration.Visible = false;

        this.btnViewJobs.Text = "View";
        btnViewConfiguration.Text = "View";
        btnDriveUsage.Text = "View";
        btnViewDatabases.Text = "View";

    }    
    protected void btnViewConfiguration_Click(object sender, EventArgs e)
    {
        if (this.btnViewConfiguration.Text == "Hide")
        {
            this.btnViewConfiguration.Text = "View";
            this.divJobs.Visible = false;
            this.DivDriveUsage.Visible = false;
            this.DivDatabases.Visible = false;
            this.DivConfiguration.Visible = false;
        }
        else
        {
            this.DivConfiguration.Visible = true;
            this.btnViewConfiguration.Visible = true;
            this.btnViewConfiguration.Text = "Hide";

            this.divJobs.Visible = false;
            this.DivDriveUsage.Visible = false;
            this.DivDatabases.Visible = false;

            btnViewJobs.Text = "View";
            btnDriveUsage.Text = "View";
            btnViewDatabases.Text = "View";
        }
    }
    protected void btnDriveUsage_Click(object sender, EventArgs e)
    {
        if (this.btnDriveUsage.Text == "Hide")
        {
           this.btnDriveUsage.Text = "View";
           this.divJobs.Visible = false;
           this.DivDriveUsage.Visible = false;
           this.DivDatabases.Visible = false;
           this.DivConfiguration.Visible = false;
        }
        else
        {
            this.DivDriveUsage.Visible = true;
            this.btnDriveUsage.Visible = true;
            this.btnDriveUsage.Text = "Hide";

            this.divJobs.Visible = false;
            this.DivDatabases.Visible = false;
            this.DivConfiguration.Visible = false;

            btnViewJobs.Text = "View";
            btnViewConfiguration.Text = "View";
            btnViewDatabases.Text = "View";
        }
    }
    protected void btnViewDatabases_Click(object sender, EventArgs e)
    {
        if (this.btnViewDatabases.Text == "Hide")
        {            
            this.btnViewDatabases.Text = "View";
            this.divJobs.Visible = false;
            this.DivDriveUsage.Visible = false;
            this.DivDatabases.Visible = false;
            this.DivConfiguration.Visible = false;
        }
        else
        {
            this.DivDatabases.Visible = true;
            this.btnViewDatabases.Visible = true;
            this.btnViewDatabases.Text = "Hide";

            this.divJobs.Visible = false;
            this.DivDriveUsage.Visible = false;
            this.DivConfiguration.Visible = false;

            btnViewJobs.Text = "View";
            btnViewConfiguration.Text = "View";
            btnDriveUsage.Text = "View";         
        }
    }    
    protected void chkboxBusinessCritical_CheckedChanged(object sender, EventArgs e)
    {
        if (chkboxBusinessCritical.Checked == true)
        {
            this.lblBusinessCriticalReason.Visible = true;
            this.txtBusinessCriticalReason.Visible = true;
            this.lblBusinsessCriticalLevel.Visible = true;
            this.ddlBusinessCriticalLevel.Visible = true;
        }
        else            
        {
            this.txtBusinessCriticalReason.Visible = false;
            this.lblBusinessCriticalReason.Visible = false;
            this.lblBusinsessCriticalLevel.Visible = false;
            this.ddlBusinessCriticalLevel.Visible = false;
        }
    }
    protected void btnViewJobs_Click(object sender, EventArgs e)
    {
        if (this.btnViewJobs.Text == "Hide")
        {
            this.btnViewJobs.Text = "View";
            this.divJobs.Visible = false;
            this.DivDriveUsage.Visible = false;
            this.DivDatabases.Visible = false;
            this.DivConfiguration.Visible = false;
        }
        else
        {
            this.divJobs.Visible = true;
            this.btnViewJobs.Visible = true;
            this.btnViewJobs.Text = "Hide";

            this.DivDriveUsage.Visible = false;
            this.DivConfiguration.Visible = false;
            this.DivDatabases.Visible = false;

            btnViewConfiguration.Text = "View";
            btnDriveUsage.Text = "View";
            btnViewDatabases.Text = "View";
        }
    }
}